/*
 * Copyright (c) 2010 CCX(China) Co.,Ltd. All Rights Reserved.
 *
 * This software is the confidential and proprietary information of
 * CCX(China) Co.,Ltd. ("Confidential Information").
 * It may not be copied or reproduced in any manner without the express 
 * written permission of CCX(China) Co.,Ltd.
 *
 * Author: zhaosj
 * Date: 2011-1-15 下午04:00:39
 */
package com.excelplugin;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import jxl.Cell;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;

import com.dbpool.BoneCPs;
import com.excelplugin.vo.FinancialReportDetail;
import com.excelplugin.vo.FinancialTemplate;
import com.excelplugin.vo.FunctionParaType;

/**
 * 导入财务报表分类信息
 * 
 * @author zhaosj
 * 
 */
public class ReportImport {

	public static String fileName = "F:\\MYDOCUMENT\\ExcelPlugin\\文档\\落地数据准备\\目录树分类信息.xls";

	/**
	 * 导入财务报表分类信息
	 */
	public void ImportReportCategory() {

		try {
			Connection con = BoneCPs.getConnection("funcOracle");
			
			// 打开EXCEL文档
			Workbook wb = Workbook.getWorkbook(new File(fileName));
			Sheet ws = wb.getSheet("Sheet1");

			Cell cell = null;
			NumberCell nc = null;

			int rows = ws.getRows(); // 文档总行数
			int cols = ws.getColumns(); // 文档总列数

			System.out.println("rows = " + rows);
			System.out.println("cols = " + cols);

			// 读取文档表单 sheet1，处理所有报表分类
			for (int r = 1; r < rows; r++) {

				// 财务报表详情信息
				FinancialReportDetail vfrd = new FinancialReportDetail();

				// 财务报表模板项
				FinancialTemplate vft = new FinancialTemplate();

				int reportStatus = 0; // 前台是否展现
				int reportOrder = 0; // 层内排序号
				int isLeaf = 0; // 是否叶子节点
				int reportOverload = 0; // 指标是否重载
				int reportUnit = 0; // 节点项单位
				int reportCategory = 0 ;//所属分类 0-通用；1-债券；2-股票；3-基金；4-指数；
				int reportModule = 0; //所属业务模块 0-通用；1-财务报表；2-实时行情；3-历史行情；4-衍生产品
				int isShowChild = 0; //是否显示子节点 0-否；1-是
				long reportId = 0; // 节点ID
				long reportParentId = 0; // 节点父ID
				String reportName = ""; // 节点名称
				String loadSheetName = ""; // 所包含函数ID的SHEET名(EXCEL中自定义的名字)
				String reportDescription = "";//说明信息
				
				//前台是否展现
				cell = ws.getCell(0, r);
				nc = (NumberCell) cell;
				reportStatus = (int) nc.getValue();

				//层内排序号
				cell = ws.getCell(1, r);
				nc = (NumberCell) cell;
				reportOrder = (int) nc.getValue();

				//节点父ID
				cell = ws.getCell(2, r);
				nc = (NumberCell) cell;
				reportParentId = (long) nc.getValue();

				//节点ID
				cell = ws.getCell(3, r);
				nc = (NumberCell) cell;
				reportId = (long) nc.getValue();

				//节点名称
				reportName = getFunctionName(ws, r);

				//是否叶子节点
				cell = ws.getCell(8, r);
				nc = (NumberCell) cell;
				isLeaf = (int) nc.getValue();

				//指标是否重载
				cell = ws.getCell(9, r);
				nc = (NumberCell) cell;
				reportOverload = (int) nc.getValue();

				// 插入报表详情表
				vfrd.setReportId(reportId);
				vfrd.setReportParentId(reportParentId);
				vfrd.setReportOrder(reportOrder);
				vfrd.setReportName(reportName);
				vfrd.setReportStatus(reportStatus);
				
				//所属分类
				cell = ws.getCell(10, r);
				nc = (NumberCell) cell;
				reportCategory = (int) nc.getValue();
				vfrd.setReportCategory(reportCategory);
				
				//说明信息
				cell = ws.getCell(11, r);
				reportDescription=cell.getContents();
				vfrd.setReportDescription(reportDescription);
				
				//所属业务模块
				cell = ws.getCell(12,r);
				nc=(NumberCell)cell;
				reportModule = (int)nc.getValue();
				vfrd.setReportModule(reportModule);
				
				//是否显示子节点
				cell = ws.getCell(13,r);
				nc=(NumberCell)cell;
				isShowChild= (int)nc.getValue();
				vfrd.setIsShowChild(isShowChild);
				
				insertVfrd(vfrd, con);

				// 如果是叶子节点，需要查找到该节点下所包含的财务报表模板，和模板中每项所对应的指标函数
				if (isLeaf == 1) {

					cell = ws.getCell(7, r);
					loadSheetName = cell.getContents();

					Sheet itemWs = wb.getSheet(loadSheetName);
					
					System.out.println(itemWs.getName());
					
					int itemRows = itemWs.getRows();

					// 如果是普通函数，首先根据SHEET名称获取到函数ID，然后从表"VBA_FUNCTION_DETAIL"中根据函数ID查找出函数名称，作为模板项的名称；
					// 该报表分类名称作为模板第一条，粗体、不缩进、无公式；所包含的函数名称，非粗体、缩进4空格、排序递增；
					if (reportOverload == 0) {

						// 获取模板表中 template_id 最大ID
						long templateId = getMaxParamterId(con, "vba_financial_template", "template_id");

						// 报表分类的名称，作为模板项的第一行
						vft.setTemplateId(templateId);
						vft.setReportParentId(reportId);
						vft.setFuncId(-1);
						vft.setReportDisplayName(loadSheetName.trim());
						vft.setReportStatus(reportStatus);
						vft.setOverstriking(1);
						vft.setHasFormula(0);
						vft.setIndentAmount(0);
						vft.setReportOrder(1);
						vft.setIsOverload(0);
						vft.setReportUnit(0);

						// insert into table
						this.insertVft(vft, con);

						// 根据loadSheetName遍历该SHEET，获取函数ID
						for (int i = 1; i < itemRows; i++) {

							long funcId = (long) ((NumberCell) itemWs.getCell(0, i)).getValue();	//指标ID
							reportUnit = (int) ((NumberCell) itemWs.getCell(1, i)).getValue();	//指标单位

							vft.setTemplateId(++templateId);
							vft.setReportParentId(reportId);
							vft.setFuncId(funcId);
							vft.setReportDisplayName(this.getDisplayNameByFuncId(funcId, con).trim());
							vft.setReportStatus((int)((NumberCell)itemWs.getCell(2,i)).getValue());
							vft.setOverstriking(0);
							vft.setHasFormula(1);
							vft.setIndentAmount(4);
							vft.setReportOrder(i + 1);
							vft.setIsOverload(0);
							vft.setReportUnit(reportUnit);

							// insert into table
							this.insertVft(vft, con);

						}
					}

					// 如果是重载函数（含有指标编码），首先根据SHEET名称获取主函数ID，然后逐行读取：名称、缩进位数、加粗设置、是否有公式、有公式读取“编码”，
					// 然后根据主函数ID和“编码”可以获取到该子函数ID，将该ID和模板项的对应关系记录入表；
					else {

						// 获取被重载函数的主函数ID
						long pFuncId = (long) ((NumberCell) itemWs.getCell(0, 0)).getValue();

						// 获取模板表中 template_id 最大ID
						long templateId = getMaxParamterId(con, "vba_financial_template", "template_id") - 1;
						// 模板项显示名称
						String displayName = "";
						// 指标编码
						int itemcode = 0;
						// 缩进空格位数
						int indentAmount = 0;
						// 是否有公式
						int hasFormula = 0;
						// 是否加粗
						int overstriking = 0;

						for (int i = 1; i < itemRows; i++) {

							vft.setTemplateId(++templateId);
							vft.setReportParentId(reportId);

							// 模板项名称
							displayName = itemWs.getCell(1, i).getContents();
							displayName = displayName.replaceAll("#", "");
							vft.setReportDisplayName(displayName.trim());

							// 缩进位数
							indentAmount = (int) ((NumberCell) itemWs.getCell(3, i)).getValue();
							vft.setIndentAmount(indentAmount);

							// 是否加粗
							overstriking = (int) ((NumberCell) itemWs.getCell(4, i)).getValue();
							vft.setOverstriking(overstriking);

							// 是否有公式
							hasFormula = (int) ((NumberCell) itemWs.getCell(5, i)).getValue();
							vft.setHasFormula(hasFormula);
							
							//报表项单位
							reportUnit = (int) ((NumberCell) itemWs.getCell(7, i)).getValue();
							vft.setReportUnit(reportUnit);

							// 重载函数的ID
							if (hasFormula == 1) {
								itemcode = (int) ((NumberCell) itemWs.getCell(2, i)).getValue();
								vft.setFuncId(this.getFuncIdByParentIdAndItemcode(pFuncId, itemcode, con));
							} else {
								vft.setFuncId(-1);
							}

							vft.setReportStatus((int)((NumberCell)itemWs.getCell(8,i)).getValue());
							vft.setIsOverload(1);
							vft.setReportOrder((int)((NumberCell)itemWs.getCell(6,i)).getValue());

							// insert into table
							this.insertVft(vft, con);

						}
					}
				}
			}

			wb.close();
			con.close();

		} catch (Exception e) {

			e.printStackTrace();
		}
	}

	/**
	 * 根据函数id获取函数名称
	 * 
	 * @param funcId
	 * @param con
	 * @return
	 */
	public String getDisplayNameByFuncId(long funcId, Connection con) {

		String funcName = "";
		try {

			String sql = "select func_name from vba_function_detail where func_id = ?";
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setLong(1, funcId);

			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				funcName = rs.getString(1);
			}

			rs.close();
			ps.close();

		} catch (Exception e) {

			e.printStackTrace();
		}

		return funcName;
	}

	/**
	 * 根据主函数ID和指标编码，获取被重载函数的ID
	 * 
	 * @param funcId
	 * @param itemcode
	 * @param con
	 * @return
	 */
	public long getFuncIdByParentIdAndItemcode(long funcId, int itemcode, Connection con) {
		long id = 0l;
		try {

			String sql = "select t.*,d.func_expression from vba_function_para_type t ,vba_function_parameter p,vba_function_detail d where t.para_id = p.para_id and d.func_id = p.func_id and p.para_name_en = 'itemcode' and d.func_id =? and t.type_code =?";
			PreparedStatement ps = con.prepareStatement(sql);

			ps.setLong(1, funcId);
			ps.setInt(2, itemcode);

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				id = rs.getLong(1);
			}

			rs.close();
			ps.close();

		} catch (Exception e) {

			e.printStackTrace();
		}
		return id;

	}

	/**
	 * 根据主函数ID和指标编码，获取报表的模板项
	 * 
	 * @param funcId
	 * @param itemcode
	 * @param con
	 * @return
	 */
	public FunctionParaType getVfptByIdAndItemcode(int funcId, int itemcode, Connection con) {

		System.out.println("getVfptByIdAndItemcode : " + funcId);

		FunctionParaType vfpt = new FunctionParaType();
		try {

			String sql = "select t.type_id from vba_function_para_type t ,vba_function_parameter p,vba_function_detail d where t.para_id = p.para_id and d.func_id = p.func_id and p.para_name_en = 'itemcode' and d.func_id =? and t.type_code =?";
			PreparedStatement ps = con.prepareStatement(sql);

			ps.setInt(1, funcId);
			ps.setInt(2, itemcode);

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				vfpt.setTypeId(rs.getInt("type_id"));
				vfpt.setParaId(rs.getInt("para_id"));
				vfpt.setTypeCode(rs.getInt("type_code"));
				vfpt.setTypeName(rs.getString("type_name"));
				vfpt.setTypeNamePy(rs.getString("type_name_py"));
				vfpt.setTypeOrder(rs.getInt("type_order"));
				vfpt.setTypeStatus(rs.getInt("type_status"));

				rs.close();
				ps.close();
			}

		} catch (Exception e) {

			e.printStackTrace();
		}

		return vfpt;
	}

	/**
	 * 根据函数ID和指标编码获取被重载的函数ID
	 * 
	 * @param funcId
	 * @param str
	 * @param con
	 * @return
	 */
	public String[] getOverloadFuncId(int funcId, String itemcodeStr, Connection con) {

		String[] aStrArray = new String[1];

		try {
			String sql = "SELECT TYPE_ID FROM VBA_FUNCTION_PARA_TYPE WHERE TYPE_CODE IN (?) AND PARA_ID = (SELECT PARA_ID FROM VBA_FUNCTION_PARAMETER WHERE FUNC_ID = ? AND PARA_NAME_EN = 'itemcode') ORDER BY 1";
			PreparedStatement ps = con.prepareStatement(sql);

			ps.setString(1, itemcodeStr);
			ps.setInt(2, funcId);

			ResultSet rs = ps.executeQuery();
			String idStr = "";

			while (rs.next()) {
				idStr += rs.getString("TYPE_ID") + ",";
			}
			if (idStr.endsWith(","))
				idStr = idStr.substring(0, idStr.length() - 1);

			aStrArray = idStr.split(",");

			rs.close();
			ps.close();

		} catch (Exception e) {

			e.printStackTrace();
		}

		return aStrArray;
	}

	/**
	 * 添加新记录：“财务报表分类信息详情表”
	 * 
	 * @param vfrd
	 * @param con
	 */
	public void insertVfrd(FinancialReportDetail vfrd, Connection con) {
		try {
			String sql = "insert into vba_financial_report_detail(report_id,report_name,report_status,REPORT_PARENT_ID,REPORT_ORDER,REPORT_CATEGORY,REPORT_DESCRIPTION,REPORT_MODULE,IS_SHOW_CHILD) values(?,?,?,?,?,?,?,?,?)";
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setLong(1, vfrd.getReportId());
			ps.setString(2, vfrd.getReportName());
			ps.setInt(3, vfrd.getReportStatus());
			ps.setLong(4, vfrd.getReportParentId());
			ps.setInt(5, vfrd.getReportOrder());
			ps.setInt(6, vfrd.getReportCategory());
			ps.setString(7, vfrd.getReportDescription());
			ps.setInt(8, vfrd.getReportModule());
			ps.setInt(9, vfrd.getIsShowChild());

			ps.executeUpdate();

			ps.close();

		} catch (Exception e) {

			e.printStackTrace();
		}
	}

	/**
	 * 添加新纪录：“财务报表模板表”
	 * 
	 * @param vft
	 * @param con
	 */
	public void insertVft(FinancialTemplate vft, Connection con) {

		try {
			String sql = "insert into vba_financial_template(template_id,report_parent_id,func_id,report_display_name,report_status,overstriking,has_formula,indent_amount,report_order,is_overload,report_unit) values(?,?,?,?,?,?,?,?,?,?,?) ";
			PreparedStatement ps = con.prepareStatement(sql);

			ps.setLong(1, vft.getTemplateId());
			ps.setLong(2, vft.getReportParentId());
			ps.setLong(3, vft.getFuncId());
			ps.setString(4, vft.getReportDisplayName());
			ps.setInt(5, vft.getReportStatus());
			ps.setInt(6, vft.getOverstriking());
			ps.setInt(7, vft.getHasFormula());
			ps.setInt(8, vft.getIndentAmount());
			ps.setInt(9, vft.getReportOrder());
			ps.setInt(10, vft.getIsOverload());
			ps.setInt(11, vft.getReportUnit());

			ps.executeUpdate();

			ps.close();

		} catch (Exception e) {

			e.printStackTrace();
		}
	}

	/**
	 * 获取报表分类名称
	 * 
	 * @param ws
	 * @param row
	 * @return
	 */
	public String getFunctionName(Sheet ws, int row) {
		Cell cell = null;
		String name = "";
		// 从第row行，第4-6列中提取报表名称（或分类名称）
		for (int c = 4; c <= 6; c++) {
			cell = ws.getCell(c, row);
			if (cell != null)
				name = cell.getContents();
			if (!"".equals(cell.getContents()))
				break;
		}

		return name;
	}

	/**
	 * 根据表名和字段名获取最大ID
	 * 
	 * @param con
	 * @param tableName
	 * @param field
	 * @return
	 */
	public long getMaxParamterId(Connection con, String tableName, String field) {
		long maxParaId = 0;
		try {
			String sql = "select max(" + field + ") as " + field + " from " + tableName;
			PreparedStatement ps = con.prepareStatement(sql);

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				maxParaId = rs.getLong(field) + 1;
			}

			rs.close();
			ps.close();

		} catch (Exception e) {
			e.printStackTrace();
		}

		return maxParaId;
	}

	public static void main(String[] args) {

		long startTime = System.currentTimeMillis();
		
		ReportImport vri = new ReportImport();
		vri.ImportReportCategory();

		long endTime = System.currentTimeMillis();
		
		System.out.println("OVER!\n"+(endTime-startTime));
	}

}
